﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

using formworks.DBO.Core;
using System.Data;
using System.Reflection;
using formworks.DBO.Attributes;

namespace formworks.DBO
{
    /// <summary>
    /// 数据库会话对象
    /// </summary>
    public class DBSession : IDBSession
    {

        #region 成员变量\属性

        

        /// <summary>
        /// 连接字符串
        /// </summary>
        private string connectionString = "";
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        private SqlConnection connection = null;
        /// <summary>
        /// 查询数据连接
        /// </summary>
        private SqlConnection connectionQuery = null;
        /// <summary>
        /// 数据库命令对象
        /// </summary>
        private SqlCommand command = null;
        /// <summary>
        /// 查询命令对象
        /// </summary>
        private SqlCommand commandQeruy = null;
        /// <summary>
        /// 事务处理对象
        /// </summary>
        private SqlTransaction transaction = null;
        /// <summary>
        /// 查询事务处理对象
        /// </summary>
        private SqlTransaction transactionQuery = null;

        //private List<object> commandBuffers = null;
        

        /// <summary>
        /// 自动提交操作，默认为自动提交。</br>
        /// 使用事务的时候会被设置为false
        /// </summary>
        public bool autoCommit { get; set;}

        /// <summary>
        /// 
        /// </summary>
        public bool sessionIsClose { get; set;}

        /// <summary>
        /// 数据库版本号
        /// </summary>
        public string DBVersion { get; set;}

        #endregion

        #region 构造函数
        
        /// <summary>
        /// 数据库会话初始化
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        public DBSession(string connectionString)
        {
            sessionIsClose = false;
            autoCommit = true;  //默认把自动提交开启

            this.connectionString = connectionString;
            connection = getConn();
            connectionQuery = getConn();
            commandQeruy = newCommand(connectionQuery);
            command = getCommand("");
        }

        /// <summary>
        /// 数据库会话初始化
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        public DBSession(System.Data.SqlClient.SqlConnection connection)
        {
            sessionIsClose = false;
            autoCommit = true;  //默认把自动提交开启

            this.connection = connection;
            this.connectionString = connection.ConnectionString;
            connectionQuery = getConn();
            commandQeruy = newCommand(connectionQuery);
            command = getCommand("");
        }
        #endregion

        #region IDBSession 成员

        /// <summary>
        /// 重启数据库会话
        /// </summary>
        public void restartSession()
        {
            sessionIsClose = false;
            connection = getConn();
            connectionQuery = getConn();
            commandQeruy = newCommand(connectionQuery);
            command = getCommand("");
            
            
            
        }


        /// <summary>
        /// 获取连接对象
        /// </summary>
        /// <returns></returns>
        public SqlConnection getConn()
        {
            try
            {
                return new SqlConnection(connectionString);
            }
            catch (System.Exception e)
            {
                throw new Exception("获取数据库连接时出错!", e);
            }

            return null;
        }

        /// <summary>
        /// 清除连接对象
        /// </summary>
        public void clearConn()
        {
            try
            {
                if (null != connection)
                    connection.Close();
                //be test//connect.Dispose();
            }
            catch (System.Exception ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                throw new Exception("关闭数据库连接时出错!", ex);
            }
            //finally
            //{
            //    if (connection.State != ConnectionState.Closed)
            //        connection.Close();
            //}   
        }

        /// <summary>
        /// 返回一个查询命令
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public SqlCommand getCommand(string sql)
        {
            SqlCommand cmd;
            try
            {
                cmd = new SqlCommand();
                //connection = getConn();
                if(connection.State == ConnectionState.Closed)
                    connection.Open();

                cmd.Connection = connection;

                //没有开启自动提交的情况下开启事务
                //if (!autoCommit)
                //{
                //    cmd.Transaction = transaction;
                //}

                cmd.CommandText = sql;

                return cmd;
            }
            catch (Exception ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                throw new Exception("获取数据库查询命令时出错!请检查数据库服务是否存在.", ex);
            }

            return null;
        }

        /// <summary>
        /// 返回一个查询命令
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public SqlCommand newCommand(SqlConnection con)
        {
            SqlCommand cmd;
            try
            {
                cmd = new SqlCommand();

                if (con.State == ConnectionState.Closed)
                    con.Open();

                cmd.Connection = con;

                return cmd;
            }
            catch (Exception ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                throw new Exception("获取数据库查询命令时出错!请检查数据库服务是否存在.", ex);
            }

            return null;
        }

        /// <summary>
        /// 关闭数据库对话
        /// </summary>
        public void closeSession()
        {
            try
            {
                if (sessionIsClose)
                {
                    return;
                }

                clearConn();
                if(connection != null)
                    connection = null;
                if (command != null)
                    command = null;
                if (commandQeruy != null)
                    commandQeruy = null;                
                if(transaction != null)
                    transaction = null;

                sessionIsClose = true;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }


        #region 事务操作
        /// <summary>
        /// 开始事务
        /// </summary>
        public void beginTransaction()
        {
            try
            {

                //connection = getConn();
                
                //if (connection.State == ConnectionState.Closed)
                //    connection.Open();

                //command = new SqlCommand();
                //command.Connection = connection;

                transaction = connection.BeginTransaction();
                command.Transaction = transaction;


                transactionQuery = connectionQuery.BeginTransaction();
                commandQeruy.Transaction = transactionQuery;
                //
                autoCommit = false; //设置为不自动提交
                //command = getCommand("");
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void rollBack()
        {
            transaction.Rollback();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void commit()
        {
            transaction.Commit();
            autoCommit = true;  //重新设置为自动提交
        }
        #endregion


        #region 通用操作

        /// <summary>
        /// 接受一个查询并返回一个SqlDataReader
        /// </summary>
        /// <param name="sqlStr">SQL查询语句</param>
        /// <returns>返回一个SqlDataReader</returns>
        public SqlDataReader executeQuery(string sql)
        {
            try
            {
                commandQeruy.CommandText = sql;
                return commandQeruy.ExecuteReader();
                //return this.getCommand(sqlStr).ExecuteReader();
            }
            catch (System.Exception ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                throw ex;
            }

            return null;
        }

        /// <summary>
        /// 执行一段SQL语句
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns>操作结果数</returns>
        public int executeNonQuery(string sql)
        {
            //SqlDataReader dread;
            try
            {
                commandQeruy.CommandText = sql;
                //int col = this.getCommand(sqlStr).ExecuteNonQuery();
                int col = commandQeruy.ExecuteNonQuery();
                connection.Close();
                return col;
            }
            catch (Exception ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                throw new Exception("无返回集合查询失败！", ex);
            }
            //finally
            //{
            //    if (connection.State != ConnectionState.Closed)
            //        connection.Close();
            //}

            return 0;
        }


        /// <summary>
        /// 保存数据库对话的所有修改
        /// </summary>
        /// <typeparam name="T">指定数据对象</typeparam>
        /// <param name="obj"></param>
        public int save<T>(T obj)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region 查询操作
        /// <summary>
        /// 以一个实体对象列表为返回，查询数据库
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> createQuery<T>(string sql)
        {
            try
            {
                SqlDataReader dr = executeQuery(sql);
                using (dr)
                {
                    List<T> list = new List<T>();
                    Type modelType = typeof(T);
                    while (dr.Read())
                    {
                        T model = Activator.CreateInstance<T>();
                        for (int i = 0; i < dr.FieldCount; i++)
                        {

                            PropertyInfo property = modelType.GetProperty(
                                dr.GetName(i)
                                );//
                            property.SetValue(model, Toolkit.checkType(dr[i], property.PropertyType), null);
                        }
                        list.Add(model);
                    }
                    return list;
                }
            }
            catch (Exception ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                throw ex;
            }
            //finally
            //{
            //    if (connection.State != ConnectionState.Closed)
            //        connection.Close();
            //}
        }

        public DataSet createQuery(string sql)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, connection);
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                
                DataSet ds = new DataSet();
                da.Fill(ds);//数据放在DataSet中
                connection.Close();
                return ds;
            }
            catch (Exception ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                throw ex;
            }
            //finally
            //{
            //    if (connection.State != ConnectionState.Closed)
            //        connection.Close();
            //}
        }

        #endregion

        #region 插入操作
        
        /// <summary>
        /// 插入一个实体数据到数据库
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int insert<T>(T obj)
        {
            try
            {
                string valueToken = "";
                string tmpFieldNameHead = "";
                string tmpFieldName = "";
                Type modelType = obj.GetType();
                PropertyInfo[] propertys = modelType.GetProperties();


                TableEntity table = (TableEntity)Attribute.GetCustomAttribute(modelType, typeof(TableEntity));
                
                //tmpFieldNameHead = propertys[0].Name;

                try
                {
                    tmpFieldNameHead = Toolkit.getPrimaryKey(propertys[0].GetCustomAttributes(
                        typeof(Attributes.Constraints), false));
                }
                catch(Exception ex)
                {
                    throw new Exception(modelType.Name + "类的Constraints 描述不存在，请检查！", ex);
                }

                //string sqlStr = "INSERT INTO " + modelType.Name + " ("
                //    + tmpFieldNameHead;

                //string tableName;
                //try
                //{
                //    tableName = ((Attributes.Description)propertys[0].GetCustomAttributes(
                //        typeof(Attributes.Description), false)[0]).name;
                //}
                //catch (Exception ex)
                //{
                //    throw new Exception(modelType.Name + "类的Description 描述不存在，请检查！", ex);
                //}
                
                //string idField = ((Constraints)propertys[0].GetCustomAttributes(typeof(Constraints), false)[0]).keyValue;


                string sqlStr = "INSERT INTO " + table.name + " ("
                    + tmpFieldNameHead;
                for (int i = 1; i < propertys.Length; i++)
                {
                    //                System.out.println( field[i].getName());
                    tmpFieldName = propertys[i].Name;
                    sqlStr += "," + tmpFieldName;
                    valueToken += ",@" + tmpFieldName;

                }
                sqlStr += ") VALUES(@" + tmpFieldNameHead + valueToken + ")";

                //
                //SqlCommand cmd = getCommand(sqlStr);
                command.CommandText = sqlStr;

                for (int iterValue = 0; iterValue < propertys.Length; iterValue++)
                {
                    command.Parameters.Add(
                        new SqlParameter(propertys[iterValue].Name,
                        propertys[iterValue].GetValue(obj, null))
                        );
                }

                return command.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                throw ex;
            }

            return -1;
        }

        public int insert<T>(string sql)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region 更新操作

        /// <summary>
        /// 更新一个实体到数据库
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int update<T>(T obj)
        {
            try
            {
                //string valueToken = "";
                //string tmpFieldNameHead = "";
                string tmpFieldName = "";
                string pkFieldName = "";
                Type modelType = obj.GetType();
                PropertyInfo[] propertys = modelType.GetProperties();
                pkFieldName = Toolkit.getPrimaryKey(propertys[0].GetCustomAttributes(
                    typeof(Attributes.Constraints), false));

                TableEntity table = (TableEntity)Attribute.GetCustomAttribute(modelType, typeof(TableEntity));

                //tmpFieldNameHead = entity2data(propertys[0].Name);
                //string tableName = ((Attributes.Description)propertys[0].GetCustomAttributes(
                //    typeof(Attributes.Description), false)[0]).name;

                String sqlStr = "UPDATE " + table.name
                    + " SET ";
                //+ propertys[0].Name 
                //+ "=@" + tmpFieldNameHead;

                //set field sql
                for (int iterField = 1; iterField < propertys.Length; iterField++)
                {
                    if (pkFieldName != propertys[iterField].Name)
                    {
                        tmpFieldName = propertys[iterField].Name;
                        sqlStr += tmpFieldName
                            + "=@" + tmpFieldName + ",";
                    }
                }

                sqlStr = sqlStr.Substring(0, sqlStr.Length - 1);
                //构建结束的字符串
                sqlStr += "  WHERE  "
                    + pkFieldName
                    + " = @" + pkFieldName;

                //
                //SqlCommand cmd = getCommand(sqlStr);
                command.CommandText = sqlStr;

                SqlParameter pkParam = null;

                for (int iterValue = 0; iterValue < propertys.Length; iterValue++)
                {
                    if (pkFieldName == propertys[iterValue].Name)
                    {
                        pkParam = new SqlParameter(
                            propertys[iterValue].Name,
                            propertys[iterValue].GetValue(obj, null));

                    }
                    else
                    {
                        command.Parameters.Add(new SqlParameter(
                            propertys[iterValue].Name,
                            propertys[iterValue].GetValue(obj, null)
                            ));
                    }
                }
                //add PK parameter
                command.Parameters.Add(pkParam);

                //
                //getPrimaryKey(propertys[0].GetCustomAttributes(typeof(PrimaryKey), false));
                //

                return command.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                throw ex;
            }
            
            return -1;
        }

        public int update<T>(string sql)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region 删除操作

        /// <summary>
        /// 删除指定ID数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public int deleteById<T>(object[] id)
        {
            try
            {
                T model = Activator.CreateInstance<T>();
                Type modelType = model.GetType();
                string pkFieldName = Toolkit.getPrimaryKey(
                    modelType.GetProperties()[0].GetCustomAttributes(
                   typeof(Attributes.Constraints), false));
                PropertyInfo[] propertys = model.GetType().GetProperties();

                TableEntity table = (TableEntity)Attribute.GetCustomAttribute(modelType, typeof(TableEntity));

                //string tableName = ((Attributes.Description)propertys[0].GetCustomAttributes(
                //   typeof(Attributes.Description), false)[0]).name;


                string sqlStr = string.Format("DELETE FROM {0} WHERE  ",
                    table.name);

                for (int iter1 = 0; iter1 < id.Length; iter1++)
                {
                    sqlStr += pkFieldName + "=@" + pkFieldName + iter1.ToString() + " or ";
                }
                //
                sqlStr = sqlStr.Substring(0, sqlStr.Length - 3);
                
                //SqlCommand cmd = getCommand(sqlStr);
                command.CommandText = sqlStr;

                for (int iter2 = 0; iter2 < id.Length; iter2++)
                {
                    command.Parameters.Add(new SqlParameter(pkFieldName + iter2.ToString(), id[iter2]));
                }

                //


                return command.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 以一个条件删除数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int delete<T>(string condition)
        {
            try
            {
                T model = Activator.CreateInstance<T>();
                PropertyInfo[] propertys = model.GetType().GetProperties();

                TableEntity table = (TableEntity)Attribute.GetCustomAttribute(model.GetType(), typeof(TableEntity));

                //string tableName = ((Attributes.Description)propertys[0].GetCustomAttributes(
                //   typeof(Attributes.Description), false)[0]).name;

                string sqlStr = string.Format("DELETE FROM {0} WHERE {1}",
                        new string[]
                {
                    table.name,
                    condition
                });

                return getCommand(sqlStr).ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 传入一个实体删除数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int delete<T>(T obj)
        {
            try
            {
                //DELETE FROM * WHERE 
                Type modelType = obj.GetType();
                PropertyInfo[] propertys = modelType.GetProperties();
                //
                string pkFieldName = Toolkit.getPrimaryKey(propertys[0].GetCustomAttributes(
                       typeof(Attributes.Constraints), false));


                TableEntity table = (TableEntity)Attribute.GetCustomAttribute(modelType, typeof(TableEntity));
                //string tableName = ((Attributes.Description)propertys[0].GetCustomAttributes(
                //   typeof(Attributes.Description), false)[0]).name;

                string sqlStr = string.Format("DELETE FROM {0} WHERE {1}=@{2}",
                    new string[]
                {
                    table.name,
                    pkFieldName,
                    pkFieldName
                });

                //SqlCommand cmd = getCommand(sqlStr);
                command.CommandText = sqlStr;

                for (int iterValue = 0; iterValue < propertys.Length; iterValue++)
                {
                    if (pkFieldName == propertys[iterValue].Name)
                    {
                        command.Parameters.Add(new SqlParameter(
                            pkFieldName,
                            propertys[iterValue].GetValue(obj, null)
                            ));
                        //
                        break;
                    }
                }


                return command.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                throw ex;
            }
            return -1;
        }
        #endregion


        #region 其他功能
        

        /// <summary>
        /// 分页查询构建,不指定列排序字段
        /// </summary>
        /// <param name="sql">要查询的sql 查询的字段要包括一个"{0},"用于替换列number </param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public string pagination(string sql, int page, int rows, int rowCount)
        {

            if (rowCount <= rows)
                return sql; //.Replace("{0}", "");
            string tmpSql = "";

            tmpSql = sql.Trim();
            string lowerCaseSql = tmpSql.ToLower();

            int oderbyIndex = lowerCaseSql.IndexOf("order by");
            string oderbyStr = "order by newid()";

            if (oderbyIndex > 1)
            {
                oderbyStr = tmpSql.Substring(oderbyIndex, tmpSql.Length - oderbyIndex);
                tmpSql = tmpSql.Substring(0, oderbyIndex);
            }



            tmpSql = "select {0} " + tmpSql.Substring(6, tmpSql.Length - 6);

            string paginationSql = "";
            string head = "";
            string content = "";
            string tail = "";

            if ( "2000" == DBVersion) //如果属于sql200的情况
            {

                int offset = lowerCaseSql.IndexOf("from") + 10;

                string tbH = tmpSql.Substring(offset, tmpSql.Length - offset);
                string tbName = tbH.Substring(0, tbH.IndexOf(" "));

                int descIndex = oderbyStr.IndexOf("desc");
                descIndex = descIndex < 1 ? oderbyStr.IndexOf("DESC") : descIndex;

                string popt = oderbyStr.ToLower().Replace("order by", "").Replace("desc", "").Replace("DESC", "");
                //
                string pv = popt;
                if (popt.IndexOf('.') > 0)
                    pv = popt.Split('.')[1].Replace("desc", "").Replace("DESC", "");

                head = string.Format("SELECT TOP {0} * FROM (", rows);

                //
                int whereIndex = lowerCaseSql.IndexOf("where") + 10;
                string whereStr = "";
                if (whereIndex > 1)
                {
                    //if (oderbyIndex > 1)
                    //    whereStr += " and " + tmpSql.Substring(whereIndex, oderbyIndex - whereIndex + 1);
                    //else
                    whereStr += " and " + tmpSql.Substring(whereIndex, tmpSql.Length - whereIndex);

                    whereStr = whereStr.Replace(tbName.Replace("[", "").Replace("]", "").Replace("\r\n", ""), "tb");
                }


                //
                if (descIndex > 1)
                {
                    content = string.Format(tmpSql,
                        string.Format(@"(select count(1) from {0} tb where  tb.{1} >= {2} {3}) AS RowNumber,",
                        tbName, pv, popt, whereStr));
                    //content = content.Replace("desc", "").Replace("DESC", "");
                }
                else
                {
                    content = string.Format(tmpSql,
                        string.Format(@"(select count(1) from {0} tb where  tb.{1} <= {2} {3}) AS RowNumber,",
                        tbName, pv, popt, whereStr));
                    //content = content.Replace("asc", "").Replace("ASC", "");
                }


                //
                //content = string.Format(tmpSql,
                //    string.Format("ROW_NUMBER() OVER ({0}) AS RowNumber,", oderbyStr));

                //
                tail = string.Format(") A WHERE RowNumber > {0}*({1}-1) order by RowNumber", rows, page);
            }
            else
            {//2k5 up
                //
                head = string.Format("SELECT TOP {0} * FROM (", rows);
                //
                content = string.Format(tmpSql,
                    string.Format("ROW_NUMBER() OVER ({0}) AS RowNumber,", oderbyStr));
                //
                tail = string.Format(") A WHERE RowNumber > {0}*({1}-1)", rows, page);
            }


            //
            paginationSql = head + content + tail;

            return paginationSql;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int paginationRowCount(string sql)
        {
            try
            {

                int count = 0;

                string head = "SELECT count(*) FROM (";
                string tail = ") A";
                string query = "";
                int oderbyIndex = sql.IndexOf("order by");
                if (oderbyIndex > 1)
                {
                    query = head + sql.Replace("{0}", "").Substring(0, oderbyIndex) + tail;
                }
                else
                {
                    query = head + sql.Replace("{0}", "") + tail;
                }

                //order by [TinyNews].[Datetime]
                //DB db = new DB();
                using (SqlDataReader dr = this.executeQuery(query))
                {
                    if (dr.Read())
                    {
                        count = dr.GetInt32(0);
                    }
                }
                //               
                return count;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #endregion
    }//end class
}//end namespace
